package com.ponxu.mb.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ponxu.db.SqlBuilder;
import com.ponxu.db.SqlRunner;
import com.ponxu.db.wrap.RowWrapper;
import com.ponxu.db.wrap.impl.IntegerRowWrapper;
import com.ponxu.mb.Global;
import com.ponxu.mb.db.ConnectionManager;
import com.ponxu.mb.model.Category;
import com.ponxu.mb.model.PageInfo;
import com.ponxu.mb.model.Post;

/**
 * 文章
 * 
 * @author xwz
 * 
 */
public class PostService {
	public static String POST_TABLE = Global.read("DB_TABLE_PREFIX") + "post";
	
	// 文章置顶状态
	public static final int TOP_YES = 1; // 置顶
	public static final int TOP_NO = 0; // 普通
	
	// 文章发布状态
	public static final Map<String, String> POST_STATUS = new HashMap<String, String>();
	static {
		POST_STATUS.put("private", "私有");
		POST_STATUS.put("protected", "密码保护");
		POST_STATUS.put("public", "已发布");
		POST_STATUS.put("draft", "草稿");
		POST_STATUS.put("auto", "自动保存");
	}
	
	// 全字段查询
	private static String fullCols = "*";
	private static RowWrapper<Post> fullRowWrapper = new RowWrapper<Post>() {
		public Post wrap(ResultSet rs) throws SQLException {
			Post post = new Post();
			post.setId(rs.getInt("id"));
			post.setTitle(rs.getString("title"));
			post.setSeoTitle(rs.getString("seo_title"));
			post.setContent(rs.getString("content"));
			post.setAbstracts(rs.getString("abstract"));
			post.setStatus(rs.getString("status"));
			post.setCommentStatus(rs.getString("comment_status"));
			post.setPageOrder(rs.getInt("page_order"));
			post.setPassword(rs.getString("password"));
			post.setPostTime(rs.getDate("post_time"));
			post.setTop(rs.getInt("top"));
			post.setType(rs.getString("type"));
			post.setUserId(rs.getInt("user_id"));
			return post;
		}
	};

	// 摘要查询
	private static String absCols = "id, title, seo_title, post_time, abstract, user_id, status, comment_status, page_order, top";
	private static RowWrapper<Post> absRowWrapper = new RowWrapper<Post>() {
		public Post wrap(ResultSet rs) throws SQLException {
			Post post = new Post();
			post.setId(rs.getInt("id"));
			post.setTitle(rs.getString("title"));
			post.setSeoTitle(rs.getString("seo_title"));
			post.setPostTime(rs.getDate("post_time"));
			post.setAbstracts(rs.getString("abstract"));
			post.setUserId(rs.getInt("user_id"));
			post.setStatus(rs.getString("status"));
			post.setCommentStatus(rs.getString("comment_status"));
			post.setPageOrder(rs.getInt("page_order"));
			post.setTop(rs.getInt("top"));
			return post;
		}
	};

	public static Post getById(int id) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		SqlBuilder.SELECT(fullCols);
		SqlBuilder.FROM(POST_TABLE);
		SqlBuilder.WHERE("id=?");

		return runner.selectOne(SqlBuilder.SQL(), fullRowWrapper, id);
	}

	public static Post getBySeoTitle(String seoTitle) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		SqlBuilder.SELECT(fullCols);
		SqlBuilder.FROM(POST_TABLE);
		SqlBuilder.WHERE("seo_tilte=?");

		return runner.selectOne(SqlBuilder.SQL(), fullRowWrapper, seoTitle);
	}

	/**
	 * 给文章列表设置 分类/标签 信息
	 */
	public static void setCategoryForPosts() {
	}

	/**
	 * 分页查询
	 * 
	 * @param page
	 * @param where
	 * @param params
	 * @return
	 */
	public static List<Post> searchPageData(PageInfo page, String where,
			Object... params) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		// 查询数据
		SqlBuilder.SELECT(absCols);
		SqlBuilder.FROM(POST_TABLE);
		SqlBuilder.WHERE(where);

		// 查询总数
		page.setTotalRow(runner.selectOne(SqlBuilder.SQL_COUNT(),
				IntegerRowWrapper.getInstance(), params));

		// 添加排序
		SqlBuilder.ORDER_BY("top desc");
		SqlBuilder.ORDER_BY("page_order desc");
		SqlBuilder.ORDER_BY("post_time desc");

		return runner.select(SqlBuilder.SQL() + page.mysqlLimit(),
				absRowWrapper, params);
	}

	/**
	 * 查询所有文章
	 * 
	 * @param page
	 * @return
	 */
	public static List<Post> searchAllPosts(PageInfo page) {
		String where = "type='post'";
		return searchPageData(page, where);
	}

	/**
	 * 查询已发布的文章
	 * 
	 * @param page
	 * @return
	 */
	public static List<Post> searchPublicPosts(PageInfo page) {
		String where = "type='post' AND status='public'";
		return searchPageData(page, where);
	}

	/**
	 * 关键字查询所有文章
	 * 
	 * @param keywords
	 * @param page
	 * @return
	 */
	public static List<Post> searchAllPostsByKeywords(String keywords,
			PageInfo page) {
		String where = "type='post' AND (title like ? OR content like ?)";
		keywords = "%" + keywords + "%";
		return searchPageData(page, where, keywords, keywords);
	}

	/**
	 * 关键字查询已发布的文章
	 * 
	 * @param keywords
	 * @param page
	 * @return
	 */
	public static List<Post> searchPublicPostsByKeywords(String keywords,
			PageInfo page) {
		String where = "type='post' AND status='public' AND (tilte like ? OR content like ?)";
		keywords = "%" + keywords + "%";
		return searchPageData(page, where, keywords, keywords);
	}

	public static List<Post> searchRandomPosts(int row) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());
		// 查询数据
		SqlBuilder.SELECT(absCols);
		SqlBuilder.FROM(POST_TABLE);

		// 添加排序
		SqlBuilder.ORDER_BY("RAND()");

		return runner.select(SqlBuilder.SQL() + " LIMIT 0, " + row,
				absRowWrapper);
	}

	public static List<Post> searchPublicPostsByCategoryId(long categoryId,
			PageInfo page) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		// 查询数据
		SqlBuilder.SELECT(absCols);
		SqlBuilder.FROM(POST_TABLE + " a");
		SqlBuilder.FROM(CategoryService.POST_CATEGORY_TABLE + " b");
		SqlBuilder.WHERE("a.type='post'");
		SqlBuilder.WHERE("a.status='public'");
		SqlBuilder.WHERE("b.category_id=?");
		SqlBuilder.WHERE("a.id=b.post_id");

		// 查询总数
		page.setTotalRow(runner.selectOne(SqlBuilder.SQL_COUNT(),
				IntegerRowWrapper.getInstance(), categoryId));

		// 添加排序
		SqlBuilder.ORDER_BY("top desc");
		SqlBuilder.ORDER_BY("page_order desc");
		SqlBuilder.ORDER_BY("post_time desc");

		return runner.select(SqlBuilder.SQL() + page.mysqlLimit(),
				absRowWrapper, categoryId);
	}

	public static List<Post> searchPublicPostsByCategoryName(
			String categorySeoName, PageInfo page) {
		Category c = CategoryService.getBySeoName(categorySeoName);
		if (c != null) {
			return searchPublicPostsByCategoryId(c.getId(), page);
		}
		return null;
	}

	public static List<Post> searchAllPages() {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		// 查询数据
		SqlBuilder.SELECT(absCols);
		SqlBuilder.FROM(POST_TABLE);
		SqlBuilder.WHERE("type='page'");
		SqlBuilder.WHERE("status='public'");

		// 添加排序
		SqlBuilder.ORDER_BY("page_order asc");
		SqlBuilder.ORDER_BY("post_time desc");

		return runner.select(SqlBuilder.SQL(), absRowWrapper);
	}

	/**
	 * 添加文章
	 * 
	 * @param post
	 */
	public static int add(Post post) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		SqlBuilder.INSERT_INTO(POST_TABLE);
		SqlBuilder.VALUES("title,seo_title,post_time,abstract,"
				+ "content,user_id,type,status,comment_status,"
				+ "password,page_order,top",
				"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?");

		String seoTitle = post.getSeoTitle();

		int id = runner.insert(SqlBuilder.SQL(), post.getTitle(), seoTitle,
				post.getPostTime(), post.getAbstracts(), post.getContent(),
				post.getUserId(), post.getType(), post.getStatus(),
				post.getCommentStatus(), post.getPassword(),
				post.getPageOrder(), post.getTop());
		if (id > 0) {
			post.setId(id);
			// 关联分类
			CategoryService.relative(id, post.getCats());
			// 关联标签
			CategoryService.relative(id, post.getTags());

			return id;
		}
		return id;
	}

	/**
	 * 更新文章
	 * 
	 * @param post
	 */
	public static int update(Post post) {
		SqlRunner runner = new SqlRunner(ConnectionManager.get());

		SqlBuilder.UPDATE(POST_TABLE);
		SqlBuilder.SET("title=?, seo_title=?, post_time=?, abstract=?, "
				+ "content=?, user_id=?, type=?, status=?, comment_status=?, "
				+ "password=?, page_order=?, top=?");
		SqlBuilder.WHERE("id=?");

		String seoTitle = post.getSeoTitle();

		int i = runner.execute(SqlBuilder.SQL(), post.getTitle(), seoTitle,
				post.getPostTime(), post.getAbstracts(), post.getContent(),
				post.getUserId(), post.getType(), post.getStatus(),
				post.getCommentStatus(), post.getPassword(),
				post.getPageOrder(), post.getTop(), post.getId());

		// 清理分类/标签的关系,以便重建
		CategoryService.clearRelationship(new int[] { post.getId() });
		// 关联分类
		CategoryService.relative(post.getId(), post.getCats());
		// 关联标签
		CategoryService.relative(post.getId(), post.getTags());

		return i;
	}

	/**
	 * 批量删除文章
	 * 
	 * @param id
	 */
	public static int deleteById(int[] ids) {
		CategoryService.clearRelationship(ids);
		CommentService.clearComments(ids);

		SqlBuilder.DELETE_FROM(POST_TABLE);
		SqlBuilder.WHERE("id in " + SqlBuilder.inClause(ids));
		return new SqlRunner(ConnectionManager.get()).execute(SqlBuilder.SQL());
	}

	/**
	 * @param ids
	 */
	public static int setTop(int[] ids, int isTop) {
		SqlBuilder.UPDATE(POST_TABLE);
		SqlBuilder.SET("top=?");
		SqlBuilder.WHERE("id in " + SqlBuilder.inClause(ids));
		return new SqlRunner(ConnectionManager.get()).execute(SqlBuilder.SQL(),
				isTop);
	}
}